from fastapi import APIRouter, Depends, HTTPException, status
from fastapi.responses import Response
from sqlalchemy.orm import Session
from sqlalchemy import func, extract, case
from app import crud, schemas
from app.database import get_db
from app.api.auth import get_current_user
from app.schemas.user import UserInDB
from app.models.issue import Issue
from app.models.case import Case
from app.models.user import User
from datetime import datetime, timedelta
from typing import Optional
import io
import pandas as pd

router = APIRouter()

# 获取统计概览数据
@router.get("/stats")
def get_stats(
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    db: Session = Depends(get_db),
    current_user: UserInDB = Depends(get_current_user)
):
    """获取系统统计数据"""
    
    # 构建查询条件
    query = db.query(Issue)
    if start_date:
        query = query.filter(Issue.created_at >= start_date)
    if end_date:
        query = query.filter(Issue.created_at <= end_date)
    
    # 总问题数
    total_issues = query.count()
    
    # 已解决问题数
    resolved_issues = query.filter(Issue.status == 'resolved').count()
    
    # 待处理问题数
    pending_issues = query.filter(Issue.status.in_(['open', 'in_progress'])).count()
    
    # 平均解决时间（简化计算，实际应该计算已解决问题的平均时间）
    avg_resolution_time = 2.5  # 暂时使用固定值
    
    return {
        "total_issues": total_issues,
        "resolved_issues": resolved_issues,
        "pending_issues": pending_issues,
        "avg_resolution_time": avg_resolution_time
    }

# 获取问题报表数据
@router.get("/issues")
def get_issue_report(
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    category: Optional[str] = None,
    db: Session = Depends(get_db),
    current_user: UserInDB = Depends(get_current_user)
):
    """获取问题报表数据"""
    
    # 按日期和分类分组统计
    query = db.query(
        func.date(Issue.created_at).label('date'),
        Issue.category,
        func.count(Issue.id).label('total_count'),
        func.sum(case([(Issue.status == 'resolved', 1)], else_=0)).label('resolved_count')
    ).group_by(func.date(Issue.created_at), Issue.category)
    
    if start_date:
        query = query.filter(Issue.created_at >= start_date)
    if end_date:
        query = query.filter(Issue.created_at <= end_date)
    if category:
        query = query.filter(Issue.category == category)
    
    results = query.all()
    
    report_data = []
    for result in results:
        resolution_rate = (result.resolved_count / result.total_count * 100) if result.total_count > 0 else 0
        report_data.append({
            "date": str(result.date),
            "category": result.category,
            "count": result.total_count,
            "resolved": result.resolved_count or 0,
            "resolution_rate": f"{resolution_rate:.1f}%"
        })
    
    return report_data

# 获取案例报表数据
@router.get("/cases")
def get_case_report(
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    category: Optional[str] = None,
    db: Session = Depends(get_db),
    current_user: UserInDB = Depends(get_current_user)
):
    """获取案例报表数据"""
    
    query = db.query(
        func.date(Case.created_at).label('date'),
        Case.category,
        func.count(Case.id).label('total_count'),
        func.sum(case([(Case.approved == True, 1)], else_=0)).label('approved_count')
    ).group_by(func.date(Case.created_at), Case.category)
    
    if start_date:
        query = query.filter(Case.created_at >= start_date)
    if end_date:
        query = query.filter(Case.created_at <= end_date)
    if category:
        query = query.filter(Case.category == category)
    
    results = query.all()
    
    report_data = []
    for result in results:
        approval_rate = (result.approved_count / result.total_count * 100) if result.total_count > 0 else 0
        report_data.append({
            "date": str(result.date),
            "category": result.category,
            "count": result.total_count,
            "approved": result.approved_count or 0,
            "approval_rate": f"{approval_rate:.1f}%"
        })
    
    return report_data

# 导出Excel报表
@router.get("/export")
def export_report(
    report_type: str = "issue",
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    category: Optional[str] = None,
    db: Session = Depends(get_db),
    current_user: UserInDB = Depends(get_current_user)
):
    """导出报表为Excel文件"""
    
    try:
        if report_type == "issue":
            # 获取问题报表数据
            data = get_issue_report(start_date, end_date, category, db, current_user)
            filename = "问题报表.xlsx"
        elif report_type == "case":
            # 获取案例报表数据
            data = get_case_report(start_date, end_date, category, db, current_user)
            filename = "案例报表.xlsx"
        else:
            raise HTTPException(status_code=400, detail="不支持的报表类型")
        
        if not data:
            raise HTTPException(status_code=404, detail="没有找到数据")
        
        # 创建DataFrame
        df = pd.DataFrame(data)
        
        # 创建Excel文件
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='报表数据', index=False)
        
        output.seek(0)
        
        # 返回Excel文件
        return Response(
            content=output.read(),
            media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            headers={
                'Content-Disposition': f'attachment; filename="{filename}"'
            }
        )
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"导出失败: {str(e)}")

# 获取用户活跃度报表
@router.get("/users")
def get_user_activity_report(
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    db: Session = Depends(get_db),
    current_user: UserInDB = Depends(get_current_user)
):
    """获取用户活跃度报表"""
    
    # 统计用户创建的问题数量
    query = db.query(
        User.username,
        func.count(Issue.id).label('issues_created')
    ).outerjoin(Issue, User.id == Issue.submitter_id)
    
    if start_date:
        query = query.filter(Issue.created_at >= start_date)
    if end_date:
        query = query.filter(Issue.created_at <= end_date)
    
    query = query.group_by(User.id, User.username)
    
    results = query.all()
    
    report_data = []
    for result in results:
        report_data.append({
            "username": result.username,
            "issues_created": result.issues_created or 0
        })
    
    return report_data